package com.lm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lm.dao.BrandDAO;
import com.lm.model.Brand;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.util.DBUtil;
import com.util.DataGeneration;

public class BrandDAOImpl implements BrandDAO, DataGeneration {
	private ComboPooledDataSource dataSource;
	private List<Brand> resList = new ArrayList<Brand>();

	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	public List getAllBrand() throws Throwable {
		return DBUtil.getCollectionOperation(dataSource,
				getSelectAllBrandSQL(), new BrandDAOImpl());
	}

	private String getSelectAllBrandSQL() {
		StringBuilder sb = new StringBuilder();
		sb.append(" select  * from lm_brand");
		return sb.toString();
	}

	public List getAllDistinctBrand() throws Throwable {
		return DBUtil.getCollectionOperation(dataSource,
				getSelectAllDistinctBrandSQL(), new BrandDAOImpl());
		
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sb = getSelectAllDistinctBrandSQL();
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb);
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Brand brand = new Brand();
				brand.setBrandNameCn(rs.getString("brandName_cn"));

				resList.add(brand);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return resList;
	}

	private String getSelectAllDistinctBrandSQL() {
		StringBuilder sb = new StringBuilder();

		sb.append(" select  distinct brandName_cn from lm_brand");
		return sb.toString();
	}

	public String[] getBrandNamesByBrandScope(String brandScope)
			throws Throwable {
		String[] brandNames = {};

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sb = getSelectBrandNamesByBrandScopeSQL(brandScope);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb);
			rs = pStmt.executeQuery();
			int i = 0;
			while (rs.next()) {
				rs.last();
				i = rs.getRow();
			}
			brandNames = new String[i];
			i = 0;
			rs.beforeFirst();
			while (rs.next()) {
				brandNames[i] = rs.getString("brandName_cn");
				i++;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}

		return brandNames;
	}

	private String getSelectBrandNamesByBrandScopeSQL(String brandScope) {
		StringBuilder sb = new StringBuilder();

		sb.append(" select brandName_cn from lm_brand  where ");
		sb.append(" id in (").append(brandScope).append(")");
		return sb.toString();
	}

	public List getBrandByTypeID(String typeID) throws Throwable {
		List resList = new ArrayList();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sb = getSelectBrandByTypeIDSQL(typeID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb);
			rs = pStmt.executeQuery();

			while (rs.next()) {
				Brand brand = new Brand();

				brand.setId(rs.getInt("id"));
				brand.setBrandNameCn(rs.getString("brandName_cn"));
				brand.setBrandNameEn(rs.getString("brandName_en"));
				resList.add(brand);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return resList;

	}

	private String getSelectBrandByTypeIDSQL(String typeID) {
		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_brand");
		sb.append(" where typeID=").append(Integer.parseInt(typeID));
		return sb.toString();
	}

	public Brand getBrandByBrandID(String brandID) throws Throwable {
		Brand brand = new Brand();

		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sb = getSelectBrandByBrandIDSQL(brandID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb);
			rs = pStmt.executeQuery();

			while (rs.next()) {
				brand.setId(rs.getInt("id"));
				brand.setBrandNameCn(rs.getString("brandName_cn"));
				brand.setBrandNameEn(rs.getString("brandName_en"));

			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return brand;
	}

	private String getSelectBrandByBrandIDSQL(String brandID) {
		StringBuilder sb = new StringBuilder();

		sb.append(" select * from lm_brand");
		sb.append(" where id=").append(Integer.parseInt(brandID));
		return sb.toString();
	}

	public void addBrand(Brand brand, String typeID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sb = getAddBrandSQL(brand, typeID);
		try {
			con = dataSource.getConnection();
			con.setAutoCommit(false);
			pStmt = con.prepareStatement(sb);
			pStmt.executeUpdate();

			con.commit();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	private String getAddBrandSQL(Brand brand, String typeID) {
		StringBuilder sb = new StringBuilder();
		sb.append(" insert into lm_brand(brandName_cn,brandName_en,typeID)");
		sb.append(" values('").append(brand.getBrandNameCn()).append("'");
		sb.append(" ,'").append(brand.getBrandNameEn()).append("'");
		sb.append(" ,").append(Integer.parseInt(typeID)).append(")");
		return sb.toString();
	}

	public void editBrandByBrandID(Brand brand, String brandID)
			throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sb = getEditBrandByBrandIDSQL(brand, brandID);
		try {
			con = dataSource.getConnection();

			pStmt = con.prepareStatement(sb);
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	private String getEditBrandByBrandIDSQL(Brand brand, String brandID) {
		StringBuilder sb = new StringBuilder();

		sb.append("update lm_brand set brandName_cn='").append(
				brand.getBrandNameCn()).append("'");
		sb.append(" ,brandName_en='").append(brand.getBrandNameEn())
				.append("'");
		sb.append(" where id=").append(Integer.parseInt(brandID));
		return sb.toString();
	}

	public void deleteBrandByBrandID(String brandID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		String sb = getDeleteBrandByBrandIDSQL(brandID);

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb);
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	private String getDeleteBrandByBrandIDSQL(String brandID) {
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_brand ");
		sb.append(" where id=").append(Integer.parseInt(brandID));
		return sb.toString();
	}

	public void deleteBrandByTypeID(String typeID) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		String sb = getDeleteBrandByTypeIDSQL(typeID);
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb);
			pStmt.executeUpdate(sb.toString());
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	private String getDeleteBrandByTypeIDSQL(String typeID) {
		StringBuilder sb = new StringBuilder();
		sb.append(" delete from lm_brand ");
		sb.append(" where typeID=").append(Integer.parseInt(typeID));
		return sb.toString();
	}

	public List<Brand> getResList(ResultSet rs) throws SQLException {
		while (rs.next()) {
			Brand brand = new Brand();
			setBrand(rs, brand);
			resList.add(brand);
		}

		return resList;
	}
	
	public List<String> getResList(ResultSet rs) throws SQLException {
		while (rs.next()) {
			String brand = new String();
			
			resList.add(rs.getString("brandName_cn"));
		}

		return resList;
	}

	private void setBrand(ResultSet rs, Brand brand) throws SQLException {
		brand.setId(rs.getInt("id"));
		brand.setBrandNameCn(rs.getString("brandName_cn"));
		brand.setBrandNameEn(rs.getString("brandName_en"));
	}

	public Brand getResModel(ResultSet rs) throws SQLException {
		Brand brand = new Brand();
		while (rs.next()) {
			setBrand(rs, brand);
		}

		return brand;
	}

}
